<?php
class Shepherd_Model_Mapper_Allocation extends Shepherd_Model_Mapper_Abstract
{
	protected $_dbName;
	
	public function __construct()
	{
		parent::__construct();
		$this->_dbName = $this->getCurrentShepherdSchema().'.allocations';
	}
	
	/**
	 * Get Db table object
	 *
	 * @return Model_DbTable_Allocation
	 */
	public function getDbTable($instanceId = null)
	{
		if (null === $this->_dbTable) {
			$this->setDbTable('Shepherd_Model_DbTable_Allocation',$instanceId);
		}
		return $this->_dbTable;
	}
	
	/**
	 * Map row object data to the site object properties
	 *
	 * @param  Zend_Db_Table_Row $row
	 * @throws Exception
	 * @return Model_Allocation mapped object
	 */
	public function mapData($row)
	{
		if (!$row instanceof Zend_Db_Table_Row) {
			throw new Exception('Invalid table row provided');
		}
		$allocation = new Shepherd_Model_Allocation();
		$allocation->setAllocationId($row['allocationid'])
					->setHardware($row['hardware'])
					->setDestination($row['destination'])
					->setStatus($row['status'])
					->setNote($row['note'])
					->setTopValue($row['top_value'])
					->setLeftValue($row['left_value'])
					->setDescription($row['description'])
					->setSystemName($row['systemname'])
					->setTopValueF($row['top_value_f'])
					->setLeftValueF($row['left_value_f'])
					->setSensorIcon($row['sensor_icon'])
					->setLayer($row['layer'])
					->setValueType($row['valuetype'])
					->setStartdt($row['startdt'])
					->setEnddt($row['enddt']);
					return $allocation;
	}
	
	/**
	 * Save Vendor object in database
	 *
	 * @param Model_Allocation $allocation
	 *
	 * @return int The primary key of the row inserted or the number of rows updated
	 */
	public function save($allocation,$instanceId = null)
	{
		parent::save($allocation);
		$data = array(
				'allocationid'  => $allocation->getAllocationId(),
				'hardware'     	=> $allocation->getHardware(),
				'destination'   => $allocation->getDestination(),
				'status'        => $allocation->getStatus(),
				'note'          => $allocation->getNote(),
				'top_value'     => $allocation->getTopValue(),
				'left_value'    => $allocation->getLeftValue(),
				'description'   => $allocation->getDescription(),
				'systemname'    => $allocation->getSystemName(),
				'top_value_f'   => $allocation->getTopValueF(),
				'left_value_f'  => $allocation->getLeftValueF(),
				'sensor_icon'   => $allocation->getSensorIcon(),
				'valuetype'     => $allocation->getValueType(),
				'layer'         => $allocation->getLayer(),
				'startdt'       => $allocation->getStartdt(),
				'enddt'         => $allocation->getEnddt()
				);
		try {
			if (NULL === ($id = $allocation->getAllocationId())) {
				unset($data['allocationid']);
				$allocation->setAllocationId($this->getDbTable($instanceId)->insert($data));
				return $allocation->getAllocationId();
			} else {
				return $this->getDbTable($instanceId)->update($data, array('allocationid = ?' => $id));
			}
		} catch (Exception $e) {
			Sss_Ultility_Common::logger()->log($e, Zend_Log::ERR);
		}
	}
	
	/**
	 * Fetch all Allocation
	 *
	 * @return array List of Model_Allocation
	 */
	public function fetchAll($instanceId = null)
	{
		$resultSet = $this->getDbTable($instanceId)->fetchAll();
		$entries   = array();
		foreach ($resultSet as $row) {
			$entries[] = $this->mapData($row);
		}
		return $entries;
	}
	
	/**
	 * Fetch a Site Allocation by Identifier
	 *
	 * @param $id
	 *
	 * @return Model_Allocation
	 */
	public function findById($id,$instanceId = null)
	{
		$select	= $this->getDbTable($instanceId)->select()->where('allocationid = ?', $id);
		$row = $this->getDbTable($instanceId)->fetchRow($select);
		$entry   = NULL;
		if (!empty($row)) {
			$entry = $this->mapData($row);
		}
		return $entry;
	}
	
	public function deleteRow($allocationId = null,$arrValueDelete = null,$instanceId = null)
	{
		if($arrValueDelete != null) {
			return $this->getDbTable($instanceId)->delete($arrValueDelete);
		} else {
			return $this->getDbTable($instanceId)->delete(array('allocationid = ?' => $allocationId));
		}
	}
	
	function getAllocationByHardware($hardwareId)
	{
		$query = '
				SELECT 
					* 
				FROM 
					'.$this->_dbName.' 
				WHERE 
					hardware ='.$hardwareId;
		return $this->fetchObject($query, true);
	}

	function checkAssignTo($hardware, $destination = null, $systemname = null)
	{
		//initializa where 
		$where = array();
		if ((int) $hardware) {
			$where[] = 'hardware=' . (int) $hardware;
		}
		if($destination != null){
			if ((int) $destination) {
				$where[] = 'destination= '. (int) $destination;
			}
		}
		if($systemname != null){
			$where[] = "systemname='$systemname'";
		}
		$where[] = "status=1";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$rows = $this->fetchObject('
									SELECT 
										* 
									FROM 
										'.$this->_dbName
									. $where
									);
		if(!$rows){
			return true;
		} else {
			return false;
		}
	}
	
	function getById($destination, $systemname=null, $instanceId=null, $hardwareId = null)
	{
		$allocationTableName = '';
		if (is_null($instanceId)) {
			$allocationTableName = $this->getCurrentShepherdSchema().'.allocations';
		} else {
			$allocationTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.allocations';
			if (!$this->checkExistedShepherdDatabase($instanceId)) {
				return false;
			}
		}
		$where= '';
		if((int)$destination) {
			$where = " WHERE 
							destination = ".$destination." 
						AND 
							systemname='$systemname'";
			if($hardwareId != null) {
				$where .= " AND hardware='$hardwareId'";
			}
			$limit = " LIMIT 1";
			$where .= $limit;
		}

		$rows = $this->fetchObject('
							SELECT 
								* 
							FROM 
								'.$allocationTableName
							. $where
		);
		return $rows;
	}

	public function getList($limit = '', $limitstart = 0, $filterParam = array(), $instanceId=null)
	{
		$allocationTableName = '';
		if (is_null($instanceId)) {
			$allocationTableName = $this->getCurrentShepherdSchema().'.allocations';
		} else {
			$allocationTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.allocations';
			if (!$this->checkExistedShepherdDatabase($instanceId)) {
				return false;
			}
		}
		//initializa where 
		$where = array();
		if ( $filterParam['hardwarename']) {
			$where[] = 'h.hardwarename LIKE "%' . $filterParam['hardwarename'].'%"';
		}
		if ( $filterParam['vendorname']) {
			$where[] = 'v.vendorname LIKE "%' . $filterParam['vendorname'].'%"';
		}
		if ( $filterParam['serial_number']) {
			$where[] = 'h.serial_number LIKE "%' . $filterParam['serial_number'].'%"';
		}
		if ($filterParam['destination']) {
			$where[] = 'al.destination = ' . (int)$filterParam['destination'];
		}
		if ($filterParam['systemname']) {
			$where[] = 'al.systemname LIKE "' . $filterParam['systemname'].'"';
		}
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';

		$getPagination = Sss_Ultility_Common::getPagination($limit, $limitstart, $filterParam['orderfield'],$filterParam['ordertype'],$filterParam['page'],'al.allocationid');
		$orderBy    = $getPagination['orderby'];
		$limit      = $getPagination['limit'];
		$limitstart = $getPagination['limitstart'];
		$pagString  = $getPagination['pagstring'];

		$query = "
				SELECT 
					al.allocationid AS allocationid, h.hardwareid AS hardwareid, 
					h.hardwarename AS hardwarename,  h.owner AS owner, s.typename AS hardwaretype, 
					v.vendorname AS vendor, h.serial_number AS serial_number, al.note AS note, 
					al.top_value, al.left_value, al.top_value_f, al.left_value_f , al.description,al.sensor_icon 
				FROM 
					" . $allocationTableName . " al 
				INNER JOIN 
					".$this->getCurrentShepherdSchema($instanceId).".containers c ON al.destination = c.containerid 
				INNER JOIN 
					".$this->_db_ovis_name.".hardware h ON al.hardware = h.hardwareid 
				LEFT JOIN 
					".$this->_db_ovis_name.".vendors v ON h.vendor = v.vendorid 
				LEFT JOIN 
					".$this->_db_ovis_name.".systemtypes s ON h.hardwaretype = s.systemtypeid 
				";
		
		//get all with paging
		$rows = $this->loadAll($query . $where . $orderBy . $pagString, false);

		//get all record not paging
		$rowAll             = $this->loadAll($query . $where . $orderBy, false);
		$result             = new stdclass();
		//return pagination object
		$result->pagination = Sss_Grid::createPagiObject($limit, $limitstart, count($rowAll));
		//return list
		$result->list       = $rows;
		return $result;
	}

	public function getAllocationById($id)
	{
		$where= '';
		if((int)$id)
		{
			$where = " WHERE allocationid = ".$id;
		}
		$rows = $this->fetchObject('
					SELECT 
						* 
					FROM 
						'.$this->_dbName
					. $where
					);
		return $rows;
	}

	public function getHardwareSettings($roomId = 0, $instanceId=null)
	{

		if (is_null($instanceId)) {
			$allocationTableName = $this->getCurrentShepherdSchema().'.allocations';
		} else {
			$allocationTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.allocations';
			if (!$this->checkExistedShepherdDatabase($instanceId)) {
				return false;
			}
		}
		$query = "
		SELECT 
			h.hardwareid, allo.valuetype, st.typename, h.vendor, ht.unitcode, st.typecode
		FROM 
			" . $allocationTableName . " `allo`
		INNER JOIN 
			" . $this->_db_ovis_name . ".systemtypes st ON allo.valuetype = st.systemtypeid
		INNER JOIN 
			" . $this->_db_ovis_name . ".hardware h ON h.hardwareid = allo.hardware
		INNER JOIN 
			" . $this->_db_ovis_name . ".hardwaretypes ht ON h.vendor = ht.vendor AND st.systemtypeid = ht.systemtype
		WHERE 
			allo.destination = $roomId 
		AND
			allo.systemname = 'ROOMS'
		AND 
			h.hardwaretype not in(".Sss_Ultility_Setting::HARDWARE_TYPE_MOBILE_NODE.",".Sss_Ultility_Setting::HARDWARE_VALUE_TYPE_NOMADIC_NODE.",".Sss_Ultility_Setting::HARDWARE_VALUE_TYPE_REFERENCE_NOTE.",".Sss_Ultility_Setting::HARDWARE_VALUE_TYPE_LOCALISATION_NODE.")";
		$hardwareList = $this->loadAll($query, true);
		return $hardwareList;
	}

	public function updateEnddtByDestination($destination,$startDt,$endDt,$instanceId = null)
	{
		$query = "
				UPDATE 
					".$this->_dbName." 
				SET 
					enddt =  '".$endDt."',startdt = '".$startDt."' 
				WHERE 
					destination = ".$destination;
		return $this->executeQuery($query,true,$instanceId);
	}
}